﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;


namespace Bitirme.DAL.Data_Access
{
    public static class EmployeeCollection
    {
        public static int InsertEmployee(Employee employee)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parName = null;
            SqlParameter parSurname = null;
            SqlParameter parEMail = null;
            SqlParameter parSalaryPerDay = null;
            SqlParameter parHourPerDay = null;
            SqlParameter parReturnValue = null;


            int insertedEmployeeID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertEmployee", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = employee.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parSurname = new SqlParameter();
                parSurname.ParameterName = "@surname";
                parSurname.Value = employee.SurName;
                parSurname.SqlDbType = SqlDbType.VarChar;

                parEMail = new SqlParameter();
                parEMail.ParameterName = "@email";
                parEMail.Value = employee.Email;
                parEMail.SqlDbType = SqlDbType.VarChar;

                parSalaryPerDay = new SqlParameter();
                parSalaryPerDay.ParameterName = "@salaryperday";
                parSalaryPerDay.Value = employee.SalaryPerDay;
                parSalaryPerDay.SqlDbType = SqlDbType.Int;

                parHourPerDay = new SqlParameter();
                parHourPerDay.ParameterName = "@hourperday";
                parHourPerDay.Value = employee.HourPerDay;
                parHourPerDay.SqlDbType = SqlDbType.Int;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";

                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parSurname);
                cmd.Parameters.Add(parEMail);
                cmd.Parameters.Add(parHourPerDay);
                cmd.Parameters.Add(parSalaryPerDay);
                cmd.Parameters.Add(parReturnValue);

                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedEmployeeID);
            }
            catch (SqlException e)
            {

                insertedEmployeeID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }


            return insertedEmployeeID;
        }

        public static Boolean UpdateEmployeeByID(Employee employee)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            SqlParameter parName = null;
            SqlParameter parSurname = null;
            SqlParameter parEMail = null;
            SqlParameter parSalaryPerDay = null;
            SqlParameter parHourPerDay = null;


            bool isupdatedEmployee = false;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("UpdateEmployeeByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = employee.ID;
                parID.SqlDbType = SqlDbType.Int;

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = employee.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parSurname = new SqlParameter();
                parSurname.ParameterName = "@surname";
                parSurname.Value = employee.SurName;
                parSurname.SqlDbType = SqlDbType.VarChar;

                parEMail = new SqlParameter();
                parEMail.ParameterName = "@email";
                parEMail.Value = employee.Email;
                parEMail.SqlDbType = SqlDbType.VarChar;

                parSalaryPerDay = new SqlParameter();
                parSalaryPerDay.ParameterName = "@salaryperday";
                parSalaryPerDay.Value = employee.SalaryPerDay;
                parSalaryPerDay.SqlDbType = SqlDbType.Int;

                parHourPerDay = new SqlParameter();
                parHourPerDay.ParameterName = "@hourperday";
                parHourPerDay.Value = employee.HourPerDay;
                parHourPerDay.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parSurname);
                cmd.Parameters.Add(parEMail);
                cmd.Parameters.Add(parHourPerDay);
                cmd.Parameters.Add(parSalaryPerDay);

                isupdatedEmployee = true;
                myConnection.Open();
                cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                isupdatedEmployee = false;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }

            return isupdatedEmployee;
        }

        public static Boolean DeleteEmployeeByID(int employeeID)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parEmployeeID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeleteEmployeeByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parEmployeeID = new SqlParameter();
                parEmployeeID.ParameterName = "@ID";
                parEmployeeID.Value = employeeID;
                parEmployeeID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parEmployeeID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }

        public static List<Employee> GetAllEmployees()
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            List<Employee> employees = new List<Employee>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetAllEmployees", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    Employee tempEmployee = new Employee();

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempEmployee.ID = ID;

                    tempEmployee.Name = SQLUtils.NVL(myReader["Name"], String.Empty).ToString();
                    tempEmployee.SurName = SQLUtils.NVL(myReader["Surname"], String.Empty).ToString();
                    tempEmployee.Email = SQLUtils.NVL(myReader["EMAIL"], String.Empty).ToString();
                    int hoursperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["HOURS_PER_DAY"], 0).ToString(), out hoursperday);
                    tempEmployee.HourPerDay = hoursperday;
                    int salaryperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["SALARY_PER_DAY"], 0).ToString(), out salaryperday);
                    tempEmployee.SalaryPerDay = salaryperday;

                    employees.Add(tempEmployee);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return employees;
        }

        public static Employee GetEmployeeByID(int employeeID)
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parProjectID = null;
            Employee tempEmployee = new Employee();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetEmployeeByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parProjectID = new SqlParameter();
                parProjectID.ParameterName = "@employeeID";
                parProjectID.Value = employeeID;
                parProjectID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parProjectID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempEmployee.ID = ID;

                    tempEmployee.Name = SQLUtils.NVL(myReader["Name"], String.Empty).ToString();
                    tempEmployee.SurName = SQLUtils.NVL(myReader["Surname"], String.Empty).ToString();
                    tempEmployee.Email = SQLUtils.NVL(myReader["EMAIL"], String.Empty).ToString();
                    int hoursperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["HOURS_PER_DAY"], 0).ToString(), out hoursperday);
                    tempEmployee.HourPerDay = hoursperday;
                    int salaryperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["SALARY_PER_DAY"], 0).ToString(), out salaryperday);
                    tempEmployee.SalaryPerDay = salaryperday;
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return tempEmployee;
        }
    
    }
}